package com.central.apps.controller;

import com.central.app.service.HandlerService;
import com.central.common.auth.details.LoginAppUser;
import com.central.common.util.SysUserUtil;
import com.central.common.web.Result;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author mengweitao
 * @Date 2022/11/30 12:10
 * 库存盘点子表数据导出
 */
@Log4j2
@RestController
@RequestMapping("/inventoryCounting")
public class InventoryCountingController {

    @Autowired
    private HandlerService handlerService;

    /**
     * 库存盘点导出
     * @param response
     * @throws IOException
     */
    @PostMapping("/inventoryCountingExportExcel/{id}")
    public void inventoryCountingExportExcel(HttpServletResponse response,@PathVariable("id") String id) throws IOException {
        try {
            //1.创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("Sheet1");
            HSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("物料编码");
            row.createCell(1).setCellValue("物料名称");
            row.createCell(2).setCellValue("库存数量");
            row.createCell(3).setCellValue("盘点人");
//            row.createCell(4).setCellValue("数量");
            row.createCell(4).setCellValue("是否已盘点");
//            row.createCell(6).setCellValue("金额");//金额是单价和数量算出来的。不用填写
            row.createCell(5).setCellValue("单价");
            row.createCell(6).setCellValue("盘点时间");
            row.createCell(7).setCellValue("货位");
            row.createCell(8).setCellValue("批次");
            row.createCell(9).setCellValue("盘点数量");
            row.createCell(10).setCellValue("差异金额");
            row.createCell(11).setCellValue("项目名称");
            row.createCell(12).setCellValue("任务名称");
            row.createCell(13).setCellValue("课题号");
            row.createCell(14).setCellValue("需求人");

            if(StringUtils.isNotBlank(id)){
                List<Map<String, Object>> bySql = handlerService.findBySql("select sysu.nickname,t.subjectno,applyuserEntity.name as applyuserEntity_name,t.`unitcost` , t.`deptid` , t.`lotnum` , t.`checker` , t.`pidd` , t.`Invtime` , t.`QRR`," +
                        "t.`proid` , t.`id` , t.`ITEMNUM` , t.`delflag` , t.`taskid` , t.`invbalanid` , t.`createuserdesc` ," +
                        "t.`differqty` , t.`linecost` , t.`createtime` , t.`CURBAL` , t.`PCSM` , t.`PDSL` , t.`binnum` , t.`SLPC` ," +
                        "t.`createuser` , t.`version` , t.`COMPANY` , t.`curbalnum` , t.`checkqty` , t.`counnot` , t.`location` , t.`auditorqty` ," +
                        "t.`updatetime` , t.`remarks` , t.`Inventory` , t.`status` , t.`updateuser` , t.`ITEMNAME` , projectEntity.proname AS 'projectEntity_proname' , projectEntity.pronum AS 'projectEntity_pronum' , taskEntity.name AS 'taskEntity_name'" +
                        " from `gq_kcpdzb` t left join gq_proaccount projectEntity on t.proid=projectEntity.id left join gq_scheduleplantask taskEntity on t.taskid=taskEntity.id LEFT JOIN gq_person applyuserEntity ON t.applyby = applyuserEntity.id LEFT JOIN sys_user sysu on sysu.id=t.createuser WHERE 1=1 and t.pidd in " + "(" + id + ")" + "order by t.createtime asc ");
                if(CollectionUtils.isNotEmpty(bySql)){
                    int rownum=1;
                    for (Map<String, Object> map : bySql) {
                        HSSFRow row1 = sheet.createRow(rownum);
                        row1.createCell(0).setCellValue(MapUtils.getString(map,"ITEMNUM"));//物料编码
                        row1.createCell(1).setCellValue(MapUtils.getString(map,"ITEMNAME"));//物料名称
                        row1.createCell(2).setCellValue(MapUtils.getString(map,"Inventory"));//库存数量
                        row1.createCell(3).setCellValue(MapUtils.getString(map,"nickname"));//盘点人
                        row1.createCell(4).setCellValue(MapUtils.getString(map,"counnot"));//是否已盘点
                        row1.createCell(5).setCellValue(MapUtils.getString(map,"unitcost"));//单价
                        row1.createCell(6).setCellValue(MapUtils.getString(map,"Invtime"));//盘点时间
                        row1.createCell(7).setCellValue(MapUtils.getString(map,"binnum"));//货位
                        row1.createCell(8).setCellValue(MapUtils.getString(map,"lotnum"));//批次
                        row1.createCell(9).setCellValue(MapUtils.getString(map,"checkqty"));//盘点数量
                        row1.createCell(10).setCellValue(MapUtils.getString(map,"linecost"));//差异金额
                        row1.createCell(11).setCellValue(MapUtils.getString(map,"projectEntity_proname"));//项目名称
                        row1.createCell(12).setCellValue(MapUtils.getString(map,"taskEntity_name"));//任务名称
                        row1.createCell(13).setCellValue(MapUtils.getString(map,"subjectno"));//课题号
                        row1.createCell(14).setCellValue(MapUtils.getString(map,"applyuserEntity_name"));//需求人
                        rownum++;
                    }
                }
            }

            //输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            String exportFileName ="库存盘点.xls";
            String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            response.setContentType("application/x-xls");
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
